SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO


CREATE PROCEDURE dbo.[DnnProduct_PWSyncDashBoard_PWCSyncStatsCheck]
	@ExcludeList NVARCHAR(1000)
AS
BEGIN
	CREATE TABLE #table (DatabaseName varchar(255))
	
	DECLARE @sql NVARCHAR(MAX)

	SET @sql = N'WITH ItemRN AS (SELECT [Name] FROM sys.databases
		WHERE [Name] NOT LIKE ''%cap%''
		and [Name] NOT LIKE ''%demo%''
		and [Name] NOT LIKE ''%dnn%''
		and [Name] NOT LIKE ''%dev%''
		and [Name] NOT LIKE ''%staging%''
		and [Name] NOT LIKE ''%sync%''
		and [Name] NOT LIKE ''%temp%''
		and [Name] NOT LIKE ''%test%''
		and [Name] NOT LIKE ''%backup%''
		and [Name] NOT LIKE ''%upgrade%''
		and [Name] NOT LIKE ''%acs%''
        and [Name] <> ''model''
		and [Name] <> ''PWRewrite''
		and [Name] <> ''adept'''
	
	IF (LEN(@ExcludeList) > 0)
		BEGIN
			SET @sql = @sql + ' and ([Name] NOT IN (' + @ExcludeList + '))'
		END
	
	SET @sql = @sql + N' ) Insert into #table (DatabaseName) SELECT [Name] FROM ItemRN ORDER BY [Name];'
	EXECUTE sp_executesql @sql

	DECLARE c CURSOR READ_ONLY
	FOR SELECT DatabaseName FROM #table

	DECLARE @db nvarchar(255)
	
	DECLARE @CAPNAME VARCHAR(100)

	OPEN c
	FETCH NEXT FROM c INTO @db
	WHILE (@@FETCH_STATUS <> -1)
	BEGIN
		DECLARE @PWAP_AppSchemas VARCHAR(255)
		DECLARE @PWAP_location VARCHAR(255)
		DECLARE @IsPWC INT
		
		SET @CAPNAME = ''
		
		SET @sql = 'SELECT @IsPWCR = COUNT(*) FROM ' + @db + '.INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = ''ServerSettings'''
		EXECUTE sp_executesql @sql, N'@IsPWCR int OUTPUT', @IsPWCR = @IsPWC OUTPUT
		
		IF (@IsPWC > 0)
			BEGIN
				
				CREATE TABLE #LatestSyncStatsID (SyncStatsID INT)
				
				SET @SQL = 'SELECT @PWAP_AppSchemasR = ServerSettingsValue FROM ' + @db + '..ServerSettings WHERE ServerSettings = ''PWAP_AppSchemas'''
				SET @PWAP_AppSchemas = ''
				
				EXECUTE sp_executesql @SQL, N'@PWAP_AppSchemasR VARCHAR(255) OUTPUT', @PWAP_AppSchemasR = @PWAP_AppSchemas OUTPUT
				SET @SQL = 'SELECT @PWAP_locationR = ServerSettingsValue FROM ' + @db + '..ServerSettings WHERE ServerSettings = ''PWAP_location'''
				
				SET @PWAP_location = ''
				
				EXECUTE sp_executesql @SQL, N'@PWAP_locationR VARCHAR(255) OUTPUT', @PWAP_locationR = @PWAP_location OUTPUT
				
				SET @CAPName = dbo.[GetCAPName](@PWAP_location)
				
				SET @SQL = 'INSERT INTO #LatestSyncStatsID (SyncStatsID)
							SELECT SyncStatsID
							FROM ' + @db + '..SyncStats WHERE SyncStatsID NOT IN 
															(
																SELECT SyncStatsID FROM DnnProduct_PWSyncDashBoard_LastSyncStatuses 
																WHERE DBName = ''' + @db + '''
															)
							ORDER BY SyncStatsID DESC'

				EXECUTE sp_executesql @SQL
				
				SET @SQL = 'INSERT INTO DnnProduct_PWSyncDashBoard_LastSyncStatuses
								([DBName], [PWVersion], [SyncStatsID], [CAPName], [PWAP_AppSchemas], [PWAP_location], [RunTime], [Succeeded], [FailedStep]
								,[FailAt], [ErrorDetail], [StepDetails], [DelRec], [ModRec], [NewRec], [Timestamp]
								)
							SELECT ''' + @db + ''', ''PWC'', SyncStatsID, ''' + @CAPNAME + ''', ''' + @PWAP_AppSchemas + ''', ''' + @PWAP_location + ''', 
									RunTime, 
									(CASE WHEN step1 = 0 OR step2 = 0 OR step3 = 0 OR step4 = 0 OR step5 = 0 OR step6 = 0 OR step7 = 0 OR step8 = 0 OR step9 = 0 OR step10 = 0 OR step11 = 0 THEN 0 ELSE 1 END) AS Succeeded,
									FailedStep, FailedAt, ErrorDetail, 
									(CONVERT(VARCHAR(1),step1) + ''|'' + CONVERT(VARCHAR(1),step2) + ''|'' + CONVERT(VARCHAR(1),step3) + ''|'' + 
										CONVERT(VARCHAR(1),step4) + ''|'' + CONVERT(VARCHAR(1),step5) + ''|'' + CONVERT(VARCHAR(1),step6) + ''|'' + 
										CONVERT(VARCHAR(1),step7) + ''|'' + CONVERT(VARCHAR(1),step8) + ''|'' + CONVERT(VARCHAR(1),step9) + ''|'' +
										CONVERT(VARCHAR(1),step10) + ''|'' + CONVERT(VARCHAR(1),step11) + ''|'' + CONVERT(VARCHAR(1),step12) + ''|'' +
										CONVERT(VARCHAR(1),step13) + ''|'' + CONVERT(VARCHAR(1),step14) + ''|'' + CONVERT(VARCHAR(1),step15) + ''|'' +
										CONVERT(VARCHAR(1),step16) + ''|'' + CONVERT(VARCHAR(1),step17)) AS StepDetails, 
									(SELECT TOP 1 DelRec FROM ' + @db + '..WebSync WHERE SyncStatsID = sstats.SyncStatsID) AS DelRec, 
									(SELECT TOP 1 ModRec FROM ' + @db + '..WebSync WHERE SyncStatsID = sstats.SyncStatsID) AS ModRec,
									(SELECT TOP 1 NewRec FROM ' + @db + '..WebSync WHERE SyncStatsID = sstats.SyncStatsID) AS NewRec,
									GETDATE()
							FROM ' + @db + '..SyncStats sstats
							WHERE SyncStatsID IN (SELECT SyncStatsID FROM #LatestSyncStatsID)
							ORDER BY SyncStatsID DESC'
				
				EXECUTE sp_executesql @SQL

				DROP TABLE #LatestSyncStatsID
				
				
			END
		FETCH NEXT FROM c INTO @db
	END

	CLOSE c
	DEALLOCATE c

	DROP TABLE #table
END
GO
